Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
GO

/*Drop stored procedures if they exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'insertEstimacionMaterial' and type = 'P')

BEGIN
    DROP PROCEDURE insertEstimacionMaterial
END

GO


/*Done droping stored procedures*/


CREATE PROCEDURE insertEstimacionMaterial ( @rowsToInsert VARCHAR (4000) ,
											@rowCount INT ,  
											@idProyecto INT , 
											@outParam INT OUTPUT						

										)
AS
	
	DECLARE @row AS VARCHAR (1000);
	DECLARE @startPos AS INT;
	DECLARE @endPos AS INT;
	DECLARE @affectedRows INT;
	DECLARE @sp INT;
	DECLARE @ep INT;	 
	DECLARE @char varchar (10);

	DECLARE @tempId INT;
	
	SET @startPos = 1;
	SET @endPos = 1;
	SET @row = 'init';	
	SET @affectedRows = 0;
	SET @char = 'x';	

	SET @sP = 1;
	SET @ep = 1;	

	SET @tempId = 1 ;

	BEGIN TRAN 

	DELETE FROM ESTIMACIONMATERIAL WHERE ESTIMACIONMATERIAL.IDPROYECTO = @idProyecto; 
	
	CREATE TABLE #tempSplit 
				( 
				  id INT PRIMARY KEY NOT NULL , 
				  c1 		INT , 
				  c2 	    INT ,
				  c3		FLOAT ,
				 )

	WHILE (@endPos != 0 AND @endPos < LEN (@rowsToInsert)  )

		BEGIN

			INSERT INTO #tempSplit VALUES (@tempId , 0 , 0 , 0 );	

			SET @endPos = (SELECT CHARINDEX (';' , @rowsToInsert , @startPos  ) ) ;
			SET @row = ( SELECT SUBSTRING (@rowsToInsert , @startPos  , ABS (@endPos - @startPos ) ) );
			
				SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
				SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );	

				update #tempSplit set c1 = CAST (@char AS INT)
				WHERE id = @tempId;

				SET @sp = @ep + 1;
			
				SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
				SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );
	
				update #tempSplit set c2 = CAST (@char AS INT)
				WHERE id = @tempId;				

				SET @sp = @ep + 1;

				/*c3 here!!*/

				SET @ep = (SELECT CHARINDEX (',' , @row , @sp  ) ) ;
				SET @char = ( SELECT SUBSTRING (@row , @sp  , ABS (@ep - @sp ) ) );
	
				update #tempSplit set c3 = CAST (@char AS FLOAT)
				WHERE id = @tempId;				

				SET @sp = @ep + 1;

			SET @startPos =  @endPos + 1;
			SET @tempId = @tempId + 1;
			SET @affectedRows = @affectedRows + 1;
		
		END;
		
		IF (@affectedRows  =   @rowCount )

			BEGIN

				INSERT INTO ESTIMACIONMATERIAL 
				SELECT c1, c2, c3 FROM #tempSplit;
				
				DROP TABLE #tempSplit;

				SET @outParam = 1 ;

				COMMIT TRAN;

			END			

		ELSE

			BEGIN	

				DROP TABLE #tempSplit;

				SET @outParam = 0 ;
			
				ROLLBACK TRAN;	

		END			

go